Install Microsoft SQL Server On CentOS Linux

配置安装源

## sql server
wget https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo -O /etc/yum.repos.d/mssql-server.repo


## mssql-tools 

wget https://packages.microsoft.com/config/rhel/7/prod.repo -O /etc/yum.repos.d/prod.repo

安装mssql server

## 安装mssql server
yum install mssql-server -y


## 执行配置脚本
## 请确保为SA帐户指定一个强密码（最小长度为8个字符，包括大写字母和小写字母，基本10位数字和/或非字母数字符号）,按提示设置SA密码。
/opt/mssql/bin/mssql-conf setup

## 验证服务是否正在运行
systemctl status mssql-server

安装命令行管理工具

## 安装SQL Server工具，如果需要可以选择安装unixODBC-utf16-devel unixODBC-devel msodbcsql
yum install mssql-tools -y


## 创建符号链接也叫软链接
ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

## 或者，配置SqlCmd环境变量

echo 'export PATH=$PATH:/opt/mssql-tools/bin' > /etc/profile.d/mssql.sh 
source /etc/profile.d/mssql.sh 

## 或者：

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile 
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc 
source ~/.bashrc 

开启防护墙端口

firewall-cmd --add-port=1433/tcp --permanent
firewall-cmd --reload

## 或者（使用iptables ）：

iptables -A INPUT -p tcp --dport 1433 -j ACCEPT
iptables-save

查询安装包

rpm -ql mssql-server
rpm -ql mssql-tool
rpm -ql msodbcsql
rpm -ql unixODBC

管理工具下载地址

https://go.microsoft.com/fwlink/?linkid=875802

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

官方安装文件

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-linux-2017

登陆测试

## 查看数据库版本
sqlcmd -S localhost -U SA -Q 'select @@VERSION'


## 可以不加-P <密码> , 后面会提示要求输入密码。
## use 切换数据库
## go 表示执行前面输入的语句
sqlcmd -S 200.200.200.50 -U sa -P your_password
> use master
> go
已将数据库上下文更改为 'master'。

## 创建数据库并查询    

> CREATE DATABASE DataTest;
> GO
> SELECT Name from sys.Databases;
> GO

命令模式执行sql

sqlcmd -S 200.200.200.50\mssqlserver -d DataTest -Q "SELECT FirstName, LastName FROM Person.Person WHERE PersonType = 'em' ORDER BY LastName, FirstName" -o C:\DataFiles\Employees.txt

命令模式执行sql文件和使用变量参数

vi EmployeeQuery2.sql

SELECT FirstName, LastName 
FROM Person.Person 
WHERE PersonType = '$(type)'
AND LastName = '$(name)'

sqlcmd -S localhost\sqlsrv2012 -d AdventureWorks2012 -i ./EmployeeQuery2.sql -v type="em" id="smith"" -o ./Employees2.txt

备份和恢复数据库

## 备份数据 demodb

sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [demodb] TO DISK = N'/var/opt/mssql/data/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

## 备份数据库 transaction log， 如果数据库运行在full recovery 模式

sqlcmd -S localhost -U SA -Q "BACKUP LOG [demodb] TO DISK = N'/var/opt/mssql/data/demodb_LogBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'demodb_LogBackup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5"

## 恢复数据库，如果不要另外恢复transaction log，就不需要添加NORECOVERY
sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [demodb] FROM DISK = N'/var/opt/mssql/data/demodb.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 10"

## 恢复数据库 transaction log
sqlcmd -S localhost -U SA -Q "RESTORE LOG demodb FROM DISK = N'/var/opt/mssql/data/demodb_LogBackup.bak'"

从windows 迁移到 centos

## 备份数据库uni_pttw

## 在centos上用sqlcmd 连上windows mssql数据，然后执行如下命令
## 也可以在windows 上用ssms备份

BACKUP DATABASE uni_pttw TO  DISK =
N'C:\java\uni_pttw.bak'
WITH NOFORMAT, NOINIT, NAME = N'uni_pttw-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

## 从windows复制文件到centos 上

## 恢复数据uni_pttw

## 在centos上用sqlcmd 连上centos上的 mssql数据，然后执行如下命令

CREATE DATABASE uni_pttw;
GO

RESTORE DATABASE uni_pttw
FROM DISK = '/var/opt/mssql/backup/uni_pttw.bak'
WITH REPLACE,
MOVE 'uni_pttw' TO '/var/opt/mssql/data/uni_pttw.mdf',
MOVE 'uni_pttw_Log' TO '/var/opt/mssql/data/uni_pttw_Log.ldf'
GO

配置变更

参考：
https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-linux-2017

所有数据库的排序规则

## 创建自定义目录及更改目录权限
mkdir -p /data/mssql_data/
chown -R mssql:mssql /data/mssql_data/

/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql_data/

systemctl restart mssql-server


## 如果需要单独更改日志的目录（如/tmp）
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /tmp/

使用存储过程来分离和加载数据库

## 分离
exec sp_detach_db db1;
go

## 加载
exec sp_attach_db 'db1','/data/mssql_data/db1.mdf','/data/mssql_data/db1_log.ldf';
go

更改SQL Server排序规则

## 查询当前数据库实例的排序规则
SELECT CONVERT(NVARCHAR(50),SERVERPROPERTY('Collation'));

## 步骤如下：

## 备份所有用户数据库。（如果是刚刚安装好的实例没有用户数据库可以跳过这一步。）
## 停止数据库实例
## 运行/opt/mssql/bin/mssql-conf set-collation命令修改排序规则
## 启动数据库实例
## 还原用户数据库（没有用户数据库可跳过）

systemctl stop mssql-server

/opt/mssql/bin/mssql-conf set-collation
Enter the collation: Chinese_PRC_CI_AS

systemctl start mssql-server


## 查询当前所有数据库的排序规则

SELECT CONVERT(NVARCHAR(30),name), CONVERT(NVARCHAR(50),collation_name) FROM sys.databases;

更改内存限制

## 单位MB
/opt/mssql/bin/mssql-conf set memory.memorylimitmb 3328

更改端口

/opt/mssql/bin/mssql-conf set network.tcpport 1444

systemctl restart mssql-server

sqlcmd -S localhost,1444 -U sa

删除设置

/opt/mssql/bin/mssql-conf unset network.tcpport
/opt/mssql/bin/mssql-conf unset memory.memorylimitmb

systemctl restart mssql-server

查看当前设置及配置文件

cat /var/opt/mssql/mssql.conf

官方配置样本

[EULA]
accepteula = Y

[coredump]
captureminiandfull = true
coredumptype = full

[filelocation]
defaultbackupdir = /var/opt/mssql/data/
defaultdatadir = /var/opt/mssql/data/
defaultdumpdir = /var/opt/mssql/data/
defaultlogdir = /var/opt/mssql/data/

[hadr]
hadrenabled = 0

[language]
lcid = 1033

[memory]
memorylimitmb = 4096

[network]
forceencryption = 0
ipaddress = 10.192.0.0
kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab
tcpport = 1401
tlscert = /etc/ssl/certs/mssql.pem
tlsciphers = ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA
tlskey = /etc/ssl/private/mssql.key
tlsprotocols = 1.2,1.1,1.0

[sqlagent]
databasemailprofile = default
errorlogfile = /var/opt/mssql/log/sqlagentlog.log
errorlogginglevel = 7

[telemetry]
customerfeedback = true
userrequestedlocalauditdirectory = /tmp/audit

[traceflag]
traceflag0 = 1204
traceflag1 = 2345
traceflag = 3456

自动安装脚本

#!/bin/bash

# Use the following variables to control your install:

# Password for the SA user (required)
MSSQL_SA_PASSWORD='<YourStrong!Passw0rd>'

# Product ID of the version of SQL server you're installing
# Must be evaluation, developer, express, web, standard, enterprise, or your 25 digit product key
# Defaults to developer
MSSQL_PID='evaluation'

# Install SQL Server Agent (recommended)
SQL_INSTALL_AGENT='y'

# Install SQL Server Full Text Search (optional)
# SQL_INSTALL_FULLTEXT='y'

# Create an additional user with sysadmin privileges (optional)
# SQL_INSTALL_USER='<Username>'
# SQL_INSTALL_USER_PASSWORD='<YourStrong!Passw0rd>'

if [ -z $MSSQL_SA_PASSWORD ]
then
echo Environment variable MSSQL_SA_PASSWORD must be set for unattended install
exit 1
fi

echo Adding Microsoft repositories...
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

echo Installing SQL Server...
sudo yum install -y mssql-server

echo Running mssql-conf setup...
sudo MSSQL_SA_PASSWORD=$MSSQL_SA_PASSWORD \
    MSSQL_PID=$MSSQL_PID \
    /opt/mssql/bin/mssql-conf -n setup accept-eula

echo Installing mssql-tools and unixODBC developer...
sudo ACCEPT_EULA=Y yum install -y mssql-tools unixODBC-devel

# Add SQL Server tools to the path by default:
echo Adding SQL Server tools to your path...
echo PATH="$PATH:/opt/mssql-tools/bin" >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# Optional SQL Server Agent installation:
if [ ! -z $SQL_INSTALL_AGENT ]
then
echo Installing SQL Server Agent...
sudo yum install -y mssql-server-agent
fi

# Optional SQL Server Full Text Search installation:
if [ ! -z $SQL_INSTALL_FULLTEXT ]
then
    echo Installing SQL Server Full-Text Search...
    sudo yum install -y mssql-server-fts
fi

# Configure firewall to allow TCP port 1433:
echo Configuring firewall to allow traffic on port 1433...
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

# Example of setting post-installation configuration options
# Set trace flags 1204 and 1222 for deadlock tracing:
#echo Setting trace flags...
#sudo /opt/mssql/bin/mssql-conf traceflag 1204 1222 on

# Restart SQL Server after making configuration changes:
echo Restarting SQL Server...
sudo systemctl restart mssql-server

# Connect to server and get the version:
counter=1
errstatus=1
while [ $counter -le 5 ] && [ $errstatus = 1 ]
do
echo Waiting for SQL Server to start...
sleep 5s
/opt/mssql-tools/bin/sqlcmd \
    -S localhost \
    -U SA \
    -P $MSSQL_SA_PASSWORD \
    -Q "SELECT @@VERSION" 2>/dev/null
errstatus=$?
((counter++))
done

# Display error if connection failed:
if [ $errstatus = 1 ]
then
echo Cannot connect to SQL Server, installation aborted
exit $errstatus
fi

# Optional new user creation:
if [ ! -z $SQL_INSTALL_USER ] && [ ! -z $SQL_INSTALL_USER_PASSWORD ]
then
echo Creating user $SQL_INSTALL_USER
/opt/mssql-tools/bin/sqlcmd \
    -S localhost \
    -U SA \
    -P $MSSQL_SA_PASSWORD \
    -Q "CREATE LOGIN [$SQL_INSTALL_USER] WITH PASSWORD=N'$SQL_INSTALL_USER_PASSWORD', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON; ALTER SERVER ROLE [sysadmin] ADD MEMBER [$SQL_INSTALL_USER]"
fi

echo Done!